"""数据库迁移工具"""

import sys
import os
from pathlib import Path
from datetime import datetime
from sqlalchemy import create_engine, text, inspect
from sqlalchemy.exc import SQLAlchemyError
import logging

# 添加项目根目录到Python路径
project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

from app.core.config.settings import settings
from app.domains.models import Base

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


class DatabaseMigration:
    """数据库迁移类"""
    
    def __init__(self):
        self.engine = create_engine(settings.DATABASE_URL, echo=settings.DATABASE_ECHO)
        self.inspector = inspect(self.engine)
    
    def get_current_tables(self):
        """获取当前数据库中的表"""
        return self.inspector.get_table_names()
    
    def get_model_tables(self):
        """获取模型定义的表"""
        return list(Base.metadata.tables.keys())
    
    def check_table_exists(self, table_name):
        """检查表是否存在"""
        return table_name in self.get_current_tables()
    
    def create_missing_tables(self):
        """创建缺失的表"""
        current_tables = set(self.get_current_tables())
        model_tables = set(self.get_model_tables())
        missing_tables = model_tables - current_tables
        
        if missing_tables:
            logger.info(f"🔨 发现缺失的表: {', '.join(missing_tables)}")
            
            # 只创建缺失的表
            for table_name in missing_tables:
                if table_name in Base.metadata.tables:
                    table = Base.metadata.tables[table_name]
                    table.create(bind=self.engine)
                    logger.info(f"✅ 表 {table_name} 创建成功")
        else:
            logger.info("📋 所有表都已存在")
    
    def backup_database(self):
        """备份数据库"""
        try:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            backup_file = f"backup_{timestamp}.sql"
            backup_path = Path("backups") / backup_file
            
            # 创建备份目录
            backup_path.parent.mkdir(exist_ok=True)
            
            # 使用mysqldump备份（需要系统安装mysqldump）
            db_url_parts = settings.DATABASE_URL.replace("mysql+pymysql://", "").split("/")
            db_info = db_url_parts[0].split("@")
            user_pass = db_info[0].split(":")
            host_port = db_info[1].split(":")
            
            username = user_pass[0]
            password = user_pass[1] if len(user_pass) > 1 else ""
            host = host_port[0]
            port = host_port[1] if len(host_port) > 1 else "3306"
            database = db_url_parts[1]
            
            cmd = f"mysqldump -h{host} -P{port} -u{username} -p{password} {database} > {backup_path}"
            
            logger.info(f"📦 数据库备份文件: {backup_path}")
            return str(backup_path)
            
        except Exception as e:
            logger.warning(f"⚠️  数据库备份失败: {e}")
            return None
    
    def add_column(self, table_name, column_definition):
        """添加列"""
        try:
            with self.engine.connect() as conn:
                conn.execute(text(f"ALTER TABLE {table_name} ADD COLUMN {column_definition}"))
                conn.commit()
                logger.info(f"✅ 列添加成功: {table_name}.{column_definition}")
        except Exception as e:
            logger.error(f"❌ 添加列失败: {e}")
            raise
    
    def drop_column(self, table_name, column_name):
        """删除列"""
        try:
            with self.engine.connect() as conn:
                conn.execute(text(f"ALTER TABLE {table_name} DROP COLUMN {column_name}"))
                conn.commit()
                logger.info(f"✅ 列删除成功: {table_name}.{column_name}")
        except Exception as e:
            logger.error(f"❌ 删除列失败: {e}")
            raise
    
    def add_index(self, table_name, index_name, columns):
        """添加索引"""
        try:
            columns_str = ', '.join(columns) if isinstance(columns, list) else columns
            with self.engine.connect() as conn:
                conn.execute(text(f"CREATE INDEX {index_name} ON {table_name} ({columns_str})"))
                conn.commit()
                logger.info(f"✅ 索引添加成功: {index_name}")
        except Exception as e:
            logger.error(f"❌ 添加索引失败: {e}")
            raise
    
    def drop_index(self, table_name, index_name):
        """删除索引"""
        try:
            with self.engine.connect() as conn:
                conn.execute(text(f"DROP INDEX {index_name} ON {table_name}"))
                conn.commit()
                logger.info(f"✅ 索引删除成功: {index_name}")
        except Exception as e:
            logger.error(f"❌ 删除索引失败: {e}")
            raise
    
    def get_table_info(self, table_name):
        """获取表信息"""
        if not self.check_table_exists(table_name):
            logger.error(f"❌ 表 {table_name} 不存在")
            return None
        
        columns = self.inspector.get_columns(table_name)
        indexes = self.inspector.get_indexes(table_name)
        foreign_keys = self.inspector.get_foreign_keys(table_name)
        
        return {
            'columns': columns,
            'indexes': indexes,
            'foreign_keys': foreign_keys
        }
    
    def compare_schemas(self):
        """比较当前数据库模式和模型定义"""
        differences = []
        
        current_tables = set(self.get_current_tables())
        model_tables = set(self.get_model_tables())
        
        # 检查缺失的表
        missing_tables = model_tables - current_tables
        if missing_tables:
            differences.append(f"缺失的表: {', '.join(missing_tables)}")
        
        # 检查多余的表
        extra_tables = current_tables - model_tables
        if extra_tables:
            differences.append(f"多余的表: {', '.join(extra_tables)}")
        
        return differences
    
    def close(self):
        """关闭数据库连接"""
        self.engine.dispose()


def main():
    """主函数"""
    import argparse
    
    parser = argparse.ArgumentParser(description='数据库迁移工具')
    parser.add_argument('command', choices=[
        'status', 'migrate', 'backup', 'compare', 'info'
    ], help='要执行的命令')
    parser.add_argument('--table', help='表名（用于info命令）')
    
    args = parser.parse_args()
    
    migration = DatabaseMigration()
    
    try:
        if args.command == 'status':
            # 显示数据库状态
            logger.info("📊 数据库状态:")
            logger.info(f"当前表数量: {len(migration.get_current_tables())}")
            logger.info(f"模型表数量: {len(migration.get_model_tables())}")
            
            differences = migration.compare_schemas()
            if differences:
                logger.warning("⚠️  发现差异:")
                for diff in differences:
                    logger.warning(f"  - {diff}")
            else:
                logger.info("✅ 数据库模式与模型定义一致")
        
        elif args.command == 'migrate':
            # 执行迁移
            logger.info("🚀 开始数据库迁移...")
            migration.create_missing_tables()
            logger.info("✅ 迁移完成")
        
        elif args.command == 'backup':
            # 备份数据库
            logger.info("📦 开始备份数据库...")
            backup_file = migration.backup_database()
            if backup_file:
                logger.info(f"✅ 备份完成: {backup_file}")
            else:
                logger.error("❌ 备份失败")
        
        elif args.command == 'compare':
            # 比较模式
            logger.info("🔍 比较数据库模式...")
            differences = migration.compare_schemas()
            if differences:
                for diff in differences:
                    logger.info(diff)
            else:
                logger.info("✅ 数据库模式与模型定义一致")
        
        elif args.command == 'info':
            # 显示表信息
            if not args.table:
                logger.error("❌ 请指定表名 --table <table_name>")
                return
            
            info = migration.get_table_info(args.table)
            if info:
                logger.info(f"📋 表 {args.table} 信息:")
                logger.info(f"列数量: {len(info['columns'])}")
                logger.info(f"索引数量: {len(info['indexes'])}")
                logger.info(f"外键数量: {len(info['foreign_keys'])}")
    
    except Exception as e:
        logger.error(f"💥 执行失败: {e}")
        sys.exit(1)
    
    finally:
        migration.close()


if __name__ == "__main__":
    main()